package de.risk.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.mysql.jdbc.PreparedStatement;

import de.risk.game.BundesLand;
import de.risk.game.City;
import de.risk.game.GameConstants;
import de.risk.geo.Point;

public class CitySQL {

	private static org.apache.log4j.Logger log = Logger
			.getLogger(CitySQL.class);

	private DB_Connector con = null;

	public CitySQL() {
		con = new DB_Connector();

		con.connectDB();
		// check if the cities tables exist
		con.isTableExisting(GameConstants.TABLE_NAME_CITIES);
		con.closeConnection();
	}

	public boolean insertCity(City city) {
		boolean inserted = false;

		con.connectDB();

		log.debug("Insert into " + GameConstants.TABLE_NAME_CITIES + " " + city.toString());

		String INSERT_RECORD = "insert into " + GameConstants.TABLE_NAME_CITIES
				+ "(id,name,bLand,einwohner,lat,lng) "
				+ "values(?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement pstmt = (PreparedStatement) DB_Connector.getCon()
					.prepareStatement(INSERT_RECORD);
			pstmt.setInt(1, city.getId());
			pstmt.setString(2, city.getName());
			pstmt.setString(3, city.getbLand());
			pstmt.setInt(4, city.getEinwohner());
			pstmt.setDouble(5, city.getLocation().getLat());
			pstmt.setDouble(6, city.getLocation().getLng());

			pstmt.executeUpdate();
			inserted = true;

		} catch (SQLException e) {
			log.error("Failed inserting city: " + city.toString());
			e.printStackTrace();
		}

		con.closeConnection();

		return inserted;
	}

	public boolean insertCities(ArrayList<City> cities) {
		for (City c : cities) {
			boolean success = insertCity(c);
			if (!success) {
				return false;
			}
		}
		return true;
	}

	public ArrayList<BundesLand> getBLaender() {
		ArrayList<BundesLand> list = new ArrayList<BundesLand>();
		con.connectDB();

		String query = "SELECT bLand, COUNT(id) " 
					 + "FROM risk_cities "
					 + "GROUP BY bLand";
		log.debug("Querying Bundeslaender");

		ResultSet rs;
		try {
			rs = DB_Connector.getStmt().executeQuery(query);
			
			int id = 1;
			while (rs.next()) {					
				String name = rs.getString(1);
				int cities = rs.getInt(2);

				BundesLand land = new BundesLand(id, name, cities);
				list.add(land);
				id++;
			}
		} catch (SQLException e) {
			log.error("Failed querying Bundeslaender");
			e.printStackTrace();
		}

		con.closeConnection();
		
		BundesLand.printArrayList(list);
		
		return list;
	}

	public ArrayList<City> getAllCities(){
		ArrayList<City> list = new ArrayList<City>();
		
		con.connectDB();

		String query = "SELECT *" 
					 + "FROM risk_cities";
		log.debug("Querying Cities");

		ResultSet rs;
		try {
			rs = DB_Connector.getStmt().executeQuery(query);
			
			while (rs.next()) {			
				int id = 		rs.getInt(1);
				String name = 	rs.getString(2);
				String bLand =	rs.getString(3);
				int einwohner = rs.getInt(4);
				Point p = new Point(rs.getDouble(5),rs.getDouble(6));

				City city = new City(id, name, bLand, einwohner, p);
				list.add(city);
			}
		} catch (SQLException e) {
			log.error("Failed querying Bundeslaender");
			e.printStackTrace();
		}

		con.closeConnection();
		
		City.printArrayList(list);		
		
		return list;
	}

	public boolean updateCity(City city){
		String executeString = "";		
		log.debug("ExecuteString = "+executeString);
		try {
			DB_Connector.getStmt().execute(executeString);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return false;
	}
}
